| EasyExcel复杂处理(合并行和列) | 您所在的位置：网站首页 › excel 多列合并 › EasyExcel复杂处理(合并行和列) |
EasyExcel复杂处理(合并行和列)
|
日常开发时客户提出了下面样式的需求，于是记录一下。
导入EasyExcel依赖 com.alibaba easyexcel 2.2.6 工具类实现EasyExcel行和列合并都需要实现CellWriteHandler 接口 行合并 public class ExcelFillCellMergePrevCol implements CellWriteHandler { private static final String KEY ="%s-%s"; //所有的合并信息都存在了这个map里面 Map mergeInfo = new HashMap(); public ExcelFillCellMergePrevCol() { } public ExcelFillCellMergePrevCol(Map mergeInfo) { this.mergeInfo = mergeInfo; } @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer integer, Integer integer1, Boolean aBoolean) { } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer integer, Boolean aBoolean) { } @Override public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) { } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List list, Cell cell, Head head, Integer integer, Boolean aBoolean) { //当前行 int curRowIndex = cell.getRowIndex(); //当前列 int curColIndex = cell.getColumnIndex(); Integer num = mergeInfo.get(String.format(KEY, curRowIndex, curColIndex)); if(null != num){ // 合并最后一行 ,列 mergeWithPrevCol(writeSheetHolder, cell, curRowIndex, curColIndex,num); } } public void mergeWithPrevCol(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex, int num) { Sheet sheet = writeSheetHolder.getSheet(); CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex, curRowIndex, curColIndex, curColIndex + num); sheet.addMergedRegion(cellRangeAddress); } //num从第几列开始增加多少列 // curRowIndex 在第几行进行行合并 // curColIndex 在第几列进行合并 // num 合并多少格 // 比如我上图中中心需要在第三行 从0列开始合并三列 所以我可以传入 (3,0,2) public void add (int curRowIndex, int curColIndex , int num){ mergeInfo.put(String.format(KEY, curRowIndex, curColIndex),num); } } 列合并 public class ExcelFillCellMergeStrategy implements CellWriteHandler { private int[] mergeColumnIndex; //数组存放这一行需要合并那几列 [0,1,2] 在这mergeRowIndex行中合并 0 、1、2列 private int mergeRowIndex; // 存放需要向上合并的列 private Integer noMergeRowIndex;// 不要合并的列 public ExcelFillCellMergeStrategy() { } public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) { this.mergeRowIndex = mergeRowIndex; this.mergeColumnIndex = mergeColumnIndex; } public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex, Integer noMergeRowIndex) { this.mergeColumnIndex = mergeColumnIndex; this.mergeRowIndex = mergeRowIndex; this.noMergeRowIndex = noMergeRowIndex; } @Override public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { } @Override public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) { } @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List list, Cell cell, Head head, Integer integer, Boolean aBoolean) { int curRowIndex = cell.getRowIndex(); int curColIndex = cell.getColumnIndex(); if (curRowIndex > mergeRowIndex) { for (int i = 0; i < mergeColumnIndex.length; i++) { if (curColIndex == mergeColumnIndex[i]) { mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex); break; } } } } /** * 当前单元格向上合并 * . * @param writeSheetHolder writeSheetHolder * @param cell 当前单元格 * @param curRowIndex 当前行 * @param curColIndex 当前列 */ private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) { Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() : cell.getNumericCellValue(); Row preRow = cell.getSheet().getRow(curRowIndex - 1); if (preRow == null) { // 当获取不到上一行数据时，使用缓存sheet中数据 preRow = writeSheetHolder.getCachedSheet().getRow(curRowIndex - 1); } Cell preCell = preRow.getCell(curColIndex); Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() : preCell.getNumericCellValue(); //不需要合并的列直接跳出 if ( (noMergeRowIndex != null) && noMergeRowIndex == (curRowIndex - 1) ){ return; } // 将当前单元格数据与上一个单元格数据比较 Boolean dataBool = preData.equals(curData); //此处需要注意：所以获取每一行第一列数据和上一行第一列数据进行比较，如果相等合并 boolean equals = cell.getRow().getCell(0).getStringCellValue().equals(cell.getSheet().getRow(curRowIndex - 1).getCell(0).getStringCellValue()); if (dataBool && equals) { Sheet sheet = writeSheetHolder.getSheet(); List mergeRegions = sheet.getMergedRegions(); boolean isMerged = false; for (int i = 0; i < mergeRegions.size() && !isMerged; i++) { CellRangeAddress cellRangeAddr = mergeRegions.get(i); // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元 if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) { sheet.removeMergedRegion(i); cellRangeAddr.setLastRow(curRowIndex); sheet.addMergedRegion(cellRangeAddr); isMerged = true; } } // 若上一个单元格未被合并，则新增合并单元 if (!isMerged) { CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex, curColIndex); sheet.addMergedRegion(cellRangeAddress); } } } } 样式工具类 public class CellStyleStrategy extends AbstractCellStyleStrategy { private WriteCellStyle headWriteCellStyle; private List contentWriteCellStyleList; private CellStyle headCellStyle; private List contentCellStyleList; public CellStyleStrategy(WriteCellStyle headWriteCellStyle, List contentWriteCellStyleList) { this.headWriteCellStyle = headWriteCellStyle; this.contentWriteCellStyleList = contentWriteCellStyleList; } public CellStyleStrategy(WriteCellStyle headWriteCellStyle, WriteCellStyle contentWriteCellStyle) { this.headWriteCellStyle = headWriteCellStyle; contentWriteCellStyleList = new ArrayList(); contentWriteCellStyleList.add(contentWriteCellStyle); } @Override protected void initCellStyle(Workbook workbook) { if (headWriteCellStyle != null) { headCellStyle = StyleUtil.buildHeadCellStyle(workbook, headWriteCellStyle); } if (contentWriteCellStyleList != null && !contentWriteCellStyleList.isEmpty()) { contentCellStyleList = new ArrayList(); for (WriteCellStyle writeCellStyle : contentWriteCellStyleList) { contentCellStyleList.add(StyleUtil.buildContentCellStyle(workbook, writeCellStyle)); } } } @Override protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) { if (headCellStyle == null) { return; } cell.setCellStyle(headCellStyle); } @Override protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) { if (contentCellStyleList == null || contentCellStyleList.isEmpty()) { return; } cell.setCellStyle(contentCellStyleList.get(0)); } } 填充数据导出 实体类由于特殊原因只展示部分，其他属性类推即可 @Data @NoArgsConstructor @ContentRowHeight(15) @HeadRowHeight(20) @ColumnWidth(20) public class TelephoneInfo { @ExcelProperty("部门") private String departmentName; @ExcelProperty("现场") @ColumnWidth(40) private String sceneName; @ExcelProperty("团队") @ColumnWidth(40) private String teamName; @ExcelProperty({"团队人力","团队人力"}) private Integer teamManpower; ExcelProperty({"团队人力","上线人力"}) private Integer onlineManpower; } 正文 void test(){ // 查询出所有的记录 List list = statisticsService.queryStatistics(agentTelephoneInfo); // 创建列合并工具类对象 ExcelFillCellMergePrevCol mergePrevCol = new ExcelFillCellMergePrevCol(); int size = list.stream().filter(item -> StringUtils.isNotEmpty(item.getFlag())&&(Integer.valueOf(item.getFlag()) < 2) ).collect(Collectors.toList()).size(); // 因为上图上方需要合并的行数不确定所以动态合并 for (int i = 0 ; i < size ; i++ ){ mergePrevCol.add( i+2,0,2); // i + 2 ? 因为表头占了两行所以要加2 不然报出不能合并的错误 A1:A2已经合并 // (i + 2 , 0 , 2) 从i+2 行操作 在第0列合并2列 } EasyExcel.write(EasyExcelUtil.getOutputStream("外呼汇总报表", response), AgentTelephoneInfo.class) .sheet("外呼汇总报表") .registerWriteHandler(horizontalCellStyleStrategyBuilder()) // 字体表格样式工具类，下方展示 .registerWriteHandler(new ExcelFillCellMergeStrategy(size,new int[]{0,1,2},size + 1)) // 行合并 // size 从这一行开始下边的向上合并 合并 0、1、2 三列 size+1 不要向上合并 // 好像直接把第一个参数 变为size + 1 貌似就可以，懒得改了，能用就行吧 》=《 .registerWriteHandler(mergePrevCol) .doWrite(list); } public CellStyleStrategy horizontalCellStyleStrategyBuilder() { WriteCellStyle headWriteCellStyle = new WriteCellStyle(); //设置头字体 WriteFont headWriteFont = new WriteFont(); headWriteFont.setFontHeightInPoints((short) 13); headWriteFont.setBold(true); headWriteCellStyle.setWriteFont(headWriteFont); //设置头居中 headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //内容策略 WriteCellStyle contentWriteCellStyle = new WriteCellStyle(); //设置 水平居中 contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER); //垂直居中 contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER); return new CellStyleStrategy(headWriteCellStyle, contentWriteCellStyle); } 最终效果：
参考：EasyExcel模板导出(行和列自动合并)_Lzfnemo2009的博客-CSDN博客_easyexcel模板导出 |
【本文地址】
公司简介
联系我们